package com.ygq;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.Test;

import java.io.FileInputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.Date;

/**
 * ExcelReadTest
 *
 * @author Yin Guiqing
 */
public class ExcelReadTest {
    static final String PATH = "src\\";

    /**
     * xls 文件的读测试
     * <p>
     * 注意读取单元格值时需要按其类型读取
     */
    @Test
    public void testRead2003() throws IOException {
        // 1. 读取文件
        FileInputStream inputStream = new FileInputStream(PATH + "英雄表2003.xls");
        // 2. 创建工作簿
        Workbook workbook = new HSSFWorkbook(inputStream);
        // 3. 获取工作表
        Sheet sheet = workbook.getSheetAt(0);
        // 4. 获取行
        Row row1 = sheet.getRow(1);

        // 5. 获取单元格并读取值，注意需要按单元格类型读取值
        String id = row1.getCell(0).getStringCellValue();
        String name = row1.getCell(1).getStringCellValue();
        String faction = row1.getCell(2).getStringCellValue();
        System.out.println(id + " " + name + " " + faction);

        // 6. 关闭流
        workbook.close();
        inputStream.close();
    }

    /**
     * xlsx 文件的读测试
     * <p>
     * 注意读取单元格值时需要按其类型读取
     */
    @Test
    public void testRead2007() throws IOException {
        // 1. 读取文件
        FileInputStream inputStream = new FileInputStream(PATH + "英雄表2007.xlsx");
        // 2. 创建工作簿
        Workbook workbook = new XSSFWorkbook(inputStream);
        // 3. 获取工作表
        Sheet sheet = workbook.getSheetAt(0);
        // 4. 获取行
        Row row1 = sheet.getRow(2);

        // 5. 获取单元格并读取值，注意需要按单元格类型读取值
        String id = row1.getCell(0).getStringCellValue();
        String name = row1.getCell(1).getStringCellValue();
        String faction = row1.getCell(2).getStringCellValue();
        System.out.println(id + " " + name + " " + faction);

        // 6. 关闭流
        workbook.close();
        inputStream.close();
    }

    /**
     * 读取不同类型的单元格
     */
    @Test
    public void testCellType() throws IOException {
        // 1. 读取文件
        FileInputStream inputStream = new FileInputStream(PATH + "明细表.xlsx");
        // 2. 创建工作簿
        Workbook workbook = new XSSFWorkbook(inputStream);
        // 3. 获取工作表
        Sheet sheet = workbook.getSheetAt(0);
        // 4. 获取标题行
        Row row0 = sheet.getRow(0);
        if (row0 != null) {
            int numberOfCells = row0.getPhysicalNumberOfCells();
            for (int i = 0; i < numberOfCells; i++) {
                Cell cell = row0.getCell(i);
                if (cell != null) {
                    String cellValue = cell.getStringCellValue();
                    System.out.print(cellValue + " | ");
                }
            }
            System.out.println();
        }

        // 获取表中内容
        int numberOfRows = sheet.getPhysicalNumberOfRows();
        for (int i = 1; i < numberOfRows; i++) {
            Row row = sheet.getRow(i);
            if (row != null) {
                int numberOfCells = row.getPhysicalNumberOfCells();
                for (int j = 0; j < numberOfCells; j++) {
                    Cell cell = row.getCell(j);
                    if (cell != null) {
                        // 注意：需要对单元格类型进行判断，不然读取会出错
                        CellType cellType = cell.getCellType();
                        switch (cellType) {
                            case STRING:
                                // 字符串（文本）单元格类型
                                System.out.println("[String]\t" + cell.getStringCellValue());
                                break;
                            case NUMERIC:
                                System.out.print("[NUMERIC]\t");
                                // 数字单元格类型（整数、小数、日期）
                                if (DateUtil.isCellDateFormatted(cell)) {
                                    // 如果是日期类型
                                    Date dateCellValue = cell.getDateCellValue();
                                    SimpleDateFormat dateFormat =
                                            new SimpleDateFormat("yyyy-MM-dd");
                                    System.out.println("[DATE]\t" + dateFormat.format(dateCellValue));
                                } else {
                                    // 如果是数字类型
                                    System.out.println(cell.getNumericCellValue());
                                }
                                break;
                            case BOOLEAN:
                                // 布尔单元格类型
                                System.out.println("[BOOLEAN]\t" + cell.getBooleanCellValue());
                                break;
                            case BLANK:
                                // 空白单元格类型
                                System.out.println("[BLANK]\t" + cell.getStringCellValue());
                                break;
                            default:
                        }
                    }
                }
            }
            System.out.println();
        }
        // 6. 关闭流
        workbook.close();
        inputStream.close();
    }

    /**
     * 读取公式单元格
     */
    @Test
    public void testFormula() throws IOException {
        FileInputStream inputStream = new FileInputStream(PATH + "公式.xlsx");
        XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
        Sheet sheet = workbook.getSheetAt(0);
        Row row = sheet.getRow(4);
        Cell cell = row.getCell(0);

        // 公式计算器 Evaluates formula cells.
        FormulaEvaluator evaluator = new XSSFFormulaEvaluator(workbook);

        CellType cellType = cell.getCellType();
        if (cellType == CellType.FORMULA) {
            // 获取公式，如 SUM(C4:E4)
            String formula = cell.getCellFormula();
            System.out.println(formula);
            // 计算公式的值
            CellValue cellValue = evaluator.evaluate(cell);
            double value = cellValue.getNumberValue();
            System.out.println(value);
        }
    }
}
